﻿CREATE PROCEDURE [dbo].[RangeReviewSetup_CheckMissings]
	@RangeReviewId INT
	, @CategoryId INT
	, @CheckSales BIT
AS

DECLARE @MissingsTable TABLE (CategoryId INT, CategoryName VARCHAR(500), SubCategoryName VARCHAR(500), ParentId INT, Attribute VARCHAR(500), Missings INT, TypeId INT)
INSERT INTO @MissingsTable (CategoryId, CategoryName, SubCategoryName, ParentId, Attribute, Missings, TypeId)
Select 
	c.Id CategoryId
	, cp.Name CategoryName
	, c.Name SubCategoryName
	, c.ParentId 
	, data.Attribute
	, data.Missings
	, 1 TypeId --Missing Attributes
From Articles a
Inner Join Categories c on a.CategoryId = c.Id
Inner Join Categories cp on c.ParentId = cp.Id
Inner Join (
	Select c.Id, 'Brand' Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Where Brand is null or Brand = '' Group By c.Id
UNION
	Select c.Id, 'Manufacturer' Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Where Manufacturer is null or Manufacturer = '' Group By c.Id
UNION
	Select c.Id, 'Attribute1' Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Where Attribute1 is null or Attribute1 = '' Group By c.Id
UNION
	Select c.Id, 'Attribute2' Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Where Attribute2 is null or Attribute2 = '' Group By c.Id
UNION
	Select c.Id, 'Attribute3' Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Where Attribute3 is null or Attribute3 = '' Group By c.Id
UNION
	Select c.Id, 'Attribute4' Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Where Attribute4 is null or Attribute4 = '' Group By c.Id
UNION
	Select c.Id, 'Attribute5' Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Where Attribute5 is null or Attribute5 = '' Group By c.Id
UNION
	Select c.Id, att.Name Attribute, COUNT(c.ParentId) Missings From Articles a Inner Join Categories c on a.CategoryId = c.Id and a.RangeReviewId = @RangeReviewId
	Inner Join AttributeTypes att on 1 = 1
	Left Outer Join ArticleAttributes aa on aa.ArticleId = a.Id
	Left Outer Join Attributes at on aa.AttributeId = at.Id and att.Id = at.AttributeTypeId
	Where aa.Id is null Group By c.Id, att.Name
) data on c.Id = data.Id
Where
	a.RangeReviewId = @RangeReviewId
	and a.IsDeletedRangeReview = 0
	and (@CategoryId = 0 OR (a.CategoryId = @CategoryId OR c.ParentId = @CategoryId))
Group By
	c.Id
	, c.Name
	, cp.Name
	, c.ParentId
	, data.Attribute
	, data.Missings
Order By
	c.Id
	, data.Attribute
	
IF @CheckSales = 1
BEGIN
	INSERT INTO @MissingsTable (CategoryId, CategoryName, SubCategoryName, ParentId, Attribute, Missings, TypeId)
	
	Select 
		c.Id
		, cp.Name
		, c.Name
		, c.ParentId
		, 'Sales For Year ' + LTRIM(STR(years.YYear)) + ' Month ' + LTRIM(STR(months.MMonth))
		, 1
		, 2 
	From Articles a
	Inner Join Categories c on a.CategoryId = c.Id
	Inner Join Categories cp on c.ParentId = cp.Id
	Inner Join (Select MMonth = number From master..spt_values Where type='P' and number between 1 and 12) months on 1 = 1
	Inner Join (Select YYear = 2000 + number From master..spt_values Where type='P' and number between 8 and 30) years on 1 = 1
	Inner Join RangeReviews rr on rr.Id = @RangeReviewId and CAST(STR(years.YYear) + '-' + STR(months.MMonth) + '-' + '01' AS Date) BETWEEN rr.FirstMonth AND DATEADD(Month, rr.TotalMonths - 1, rr.FirstMonth)
	LEFT OUTER JOIN 
		(
		Select a.CategoryId, YEAR(sales.Date) YYear, MONTH(sales.Date) MMonth, SUM(sales.Sales) Sales From Articles a 
		INNER JOIN Categories c on a.CategoryId = c.Id
		INNER JOIN RangeReviews rr on a.RangeReviewId = rr.Id and rr.Id = @RangeReviewId
		INNER JOIN ArticleSales sales on a.Id = sales.ArticleId 
			AND (sales.Date BETWEEN rr.FirstMonth AND DATEADD(Month, rr.TotalMonths - 1, rr.FirstMonth))
			AND sales.Confirmed = 1 
			AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId)
		Where
			(@CategoryId = 0 OR (a.CategoryId = @CategoryId OR c.ParentId = @CategoryId))
		Group By
			a.CategoryId
			, YEAR(sales.Date)
			, MONTH(sales.Date)
		) sales on c.Id = sales.CategoryId and years.YYear = sales.YYear and months.MMonth = sales.MMonth
	Where 
		a.RangeReviewId = @RangeReviewId
		and c.Level = 3 
		and sales.CategoryId IS NULL
		and (@CategoryId = 0 OR (c.Id = @CategoryId OR c.ParentId = @CategoryId))
	order by c.Id, years.YYear, months.MMonth

END

Select CategoryId, CategoryName, SubCategoryName, ParentId, Attribute, Missings, TypeId From @MissingsTable
